package checkDetail;

import general.BaseBean;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.json.JSONObject;

import weaver.conn.RecordSet;
import weaver.general.GCONST;
import weaver.general.Util;

public class TriggerCheck extends BaseBean implements CheckBase  {
    
    /**
     * workflow_requestbase表对应触发器名称
     */
    private static String requestbaseTrigger = "WORKFLOW_REQUESTBASE_GETPINYIN";
    
    /**
     * ORACLE_workflow_base表中触发器list集合
     */
    private static List<String> baseTriggerList = new ArrayList<String>();
    
    /**
     * ORACLE_workflow_currentoperator表中触发器list集合
     */
    private static List<String> currentTriggerList = new ArrayList<String>();
    
    private static Statement statement = null;
    
    static {
        currentTriggerList.add("T_WF_CUROPT_TRIGGER");
        currentTriggerList.add("CURRENTOPERATORCHG_TRIGGER");
        
        baseTriggerList.add("WORKFLOW_BASE_GETPINYIN");
        baseTriggerList.add("WORKFLOW_BASE_TRIGGER");
        baseTriggerList.add("WORKFLOWBASETIMESTAMP_TRI");
    }
    
    /**
     * ORACLE_定义一个静态资源标准触发器内容Map<trigger, triggerText>
     */
    private static Map<String, String> triggerTextMap = new HashMap<String, String>();
    
    /**
     * sqlserver_定义一个静态资源标准触发器内容Map<trigger, triggerText>
     */
    private static Map<String, String> serverTriggerTextMap = new HashMap<String, String>();
    
    static {
        triggerTextMap.put("WORKFLOW_BASE_GETPINYIN", "TRIGGER \"&user&\".\"WORKFLOW_BASE_GETPINYIN\" before insert or update of workflowname on workflow_base for each row begin select Lower(getpinyin((:new.workflowname))) into :new.ecology_pinyin_search from dual; end;");
        triggerTextMap.put("WORKFLOW_BASE_TRIGGER", "TRIGGER \"&user&\".\"WORKFLOW_BASE_TRIGGER\" before insert on workflow_base for each row begin select workflow_base_id.nextval into :new.id from dual; end;");
        triggerTextMap.put("WORKFLOWBASETIMESTAMP_TRI", "TRIGGER \"WORKFLOWBASETIMESTAMP_TRI\" AFTER INSERT OR DELETE OR UPDATE ON workflow_base FOR EACH ROW BEGIN if INSERTING or DELETING then update mobileSyncInfo set syncLastTime = (SYSDATE - TO_DATE('1970-1-1', 'YYYY-MM-DD')) * 86400 where syncTable = 'WorkFlowType'; elsif :new.id <> :old.id or NVL(:new.workflowname, ' ') <> NVL(:old.workflowname, ' ') or :new.workflowtype <> :old.workflowtype or NVL(:new.isvalid, '0') <> NVL(:old.isvalid, '0') or NVL(:new.isbill, '0') <> NVL(:old.isbill, '0') then update mobileSyncInfo set syncLastTime = (SYSDATE - TO_DATE('1970-1-1', 'YYYY-MM-DD')) * 86400 where syncTable = 'WorkFlowType'; end if; END;");
        
        triggerTextMap.put("T_WF_CUROPT_TRIGGER", "TRIGGER \"&user&\".\"T_WF_CUROPT_TRIGGER\" before insert on workflow_currentoperator for each row begin select T_wf_curopt_id.nextval into :new.id from dual; end;");
        triggerTextMap.put("CURRENTOPERATORCHG_TRIGGER", "TRIGGER CurrentoperatorChg_TRIGGER AFTER UPDATE OR INSERT OR DELETE ON workflow_currentoperator FOR each row declare MODTYPE_1 VARCHAR2(4000); DOCCREATEDATE_1 VARCHAR2(4000); DOCID INTEGER; begin IF INSERTING THEN MODTYPE_1 := 'INSERT'; DOCCREATEDATE_1 := TO_CHAR(sysdate , 'YYYY-MM-DD'); DOCID := :new.requestid; ELSIF UPDATING THEN MODTYPE_1 := 'UPDATE'; DOCCREATEDATE_1 := TO_CHAR(sysdate , 'YYYY-MM-DD'); DOCID := :old.requestid; ELSIF DELETING THEN MODTYPE_1 := 'UPDATE'; DOCCREATEDATE_1 := TO_CHAR(sysdate , 'YYYY-MM-DD'); DOCID := :old.requestid; end if;  insert into indexupdatelog(ID, DOCID, CTYPE, MODTYPE, CREATETIME,DOCCREATEDATE, DONEFLAG) values(indexupdatelog_Id.nextval, DOCID, 'WF', MODTYPE_1, TO_CHAR(sysdate , 'YYYY-MM-DD HH24:MI:SS'),DOCCREATEDATE_1, 0); end;");
        
        triggerTextMap.put("WORKFLOW_REQUESTBASE_GETPINYIN", "TRIGGER \"&user&\".\"WORKFLOW_REQUESTBASE_GETPINYIN\" before insert or update of requestname on workflow_requestbase for each row begin select Lower(getpinyin((:new.requestname))) into :new.ecology_pinyin_search from dual; end;");
        
        triggerTextMap.put("formtable_main_ID_TR", "TRIGGER &triggername& before insert on &table& for each row begin select &triggernameid&.nextval into :new.id from dual; end;");
        triggerTextMap.put("formtable_main_ID_TRIGGER", "TRIGGER \"&user&\".\"&triggername&\" before insert on &table& for each row begin select &triggernameid&.nextval into :new.id from dual; end;");
        
        serverTriggerTextMap.put("WORKFLOWBASETIMESTAMP_TRIGGER", "CREATE trigger WorkFlowBaseTimesTamp_trigger ON workflow_base FOR INSERT, DELETE, UPDATE AS BEGIN if not exists(select 1 from inserted) or not exists(select 1 from deleted) begin update mobileSyncInfo set syncLastTime=DATEDIFF(ss, '1970-01-01', GETDATE()) where syncTable='WorkFlowType' end else begin if not exists(select 1 from inserted as ins,deleted as del where ins.id=del.id and ISNULL(ins.workflowname,' ')=ISNULL(del.workflowname,' ') and ins.workflowtype=del.workflowtype and ISNULL(ins.isvalid,'0')=ISNULL(del.isvalid,'0') and ISNULL(ins.isbill,'0')=ISNULL(del.isbill,'0')) begin update mobileSyncInfo set syncLastTime=DATEDIFF(ss, '1970-01-01', GETDATE()) where syncTable='WorkFlowType' end end END");
        serverTriggerTextMap.put("WORKFLOW_BASE_GETPINYIN", "CREATE trigger workflow_base_getpinyin ON workflow_base FOR INSERT,UPDATE AS DECLARE @pinyinlastname varchar(4000) DECLARE @id_1 int begin if (update(workflowname)) begin SELECT @id_1 = id,@pinyinlastname = lower([dbo].[getPinYin](workflowname)) FROM inserted update workflow_base set ecology_pinyin_search= @pinyinlastname where id = @id_1 end end");
        serverTriggerTextMap.put("WORKFLOW_REQUESTBASE_GETPINYIN", "CREATE trigger workflow_requestbase_getpinyin ON workflow_requestbase FOR INSERT,UPDATE AS DECLARE @pinyinlastname varchar(4000) DECLARE @id_1 int begin if (update(requestname)) begin SELECT @id_1 = requestid,@pinyinlastname = lower([dbo].[getPinYin](requestname)) FROM inserted update workflow_requestbase set ecology_pinyin_search= @pinyinlastname where requestid = @id_1 end end");
    }

    @Override
    public JSONObject checkData(HashMap dataMap) {
        
        String poolName = GCONST.getServerName();
        //链接的数据库用户名
        String user = getPropValue(GCONST.getConfigFile() , poolName + ".user").toUpperCase();
        
        setRecordSet();
        (new BaseBean()).writeLog("TriggerCheck checkData start");
        List resultList = new ArrayList();
        String result = "";
        String dbType = recordSet.getDBType();
        
        //判断数据库类型
        if("oracle".equals((dbType))){
            //根据对应表名来查询有哪些触发器
            String tableTriggerSql = "select trigger_name from all_triggers where table_name = '%table%' and owner = '" + user + "'";
            
            //检测WORKFLOW_BASE表中触发器
            checkTableTrigger(resultList, tableTriggerSql, "WORKFLOW_BASE", 3, user);
            
            //检测WORKFLOW_CURRENTOPERATOR表中触发器
            checkTableTrigger(resultList, tableTriggerSql, "WORKFLOW_CURRENTOPERATOR", 2, user);
            
            //检测WORKFLOW_REQUESTBASE表中触发器
            checkTableTrigger(resultList, tableTriggerSql, "WORKFLOW_REQUESTBASE", 1, user);
            
            //检测所有表单formtable_main_中触发器名称和内容
            checkFormTableTrigger(resultList, user);
        }else if("sqlserver".equals((dbType))) {
            //根据对应表名来查询有哪些触发器
            String tableTriggerSql = "EXEC sp_helptrigger '%table%'";
            
            //检测WORKFLOW_BASE表触发器
            sqlServerTriCheck(resultList, tableTriggerSql, "WORKFLOW_BASE", 2);
            
            //检测WORKFLOW_REQUESTBASE表触发器
            sqlServerTriCheck(resultList, tableTriggerSql, "WORKFLOW_REQUESTBASE", 1);
        }
        
        if(resultList.size() == 0){
            result = resultOk;
            resultList.add("没有检测数据库表触发器的问题。");
        }else{
            result = resultNeedFix;
        }
        (new BaseBean()).writeLog("TriggerCheck checkData end");
        return formatReturnJsonCheck(result, resultList, this);
    }

    private void checkFormTableTrigger(List resultList, String owner) {
        
        //查询自定义表单中没有触发器的表
        String sql1 = "select c.tablename from (select UPPER(tablename) as tablename from workflow_bill where id < 0 and UPPER(tablename) like 'FORMTABLE_MAIN%' ";
        sql1 += " union select UPPER(tablename) as tablename from workflow_billdetailtable where billid < 0 and UPPER(tablename) like 'FORMTABLE_MAIN%') c"; 
        sql1 += " where not exists (select 1 from all_triggers b where b.owner = '" + owner + "' and b.TABLE_NAME = c.tablename)";
        sql1 += " and ( select count(*) from user_tables where table_name = c.tablename ) = 1";
        recordSet.executeSql(sql1);
        //如果不为空，则表示自定义表单中存在无触发器的数据
        while(recordSet.next()){
            String tableName = recordSet.getString("tablename");
            Map resultMap = new HashMap<String, String>();
            resultMap.put("showMessage", tableName + "表中没有触发器 ，请单个修复");
            resultMap.put("buttonname","修复");
            resultMap.put("tableName",tableName);
            resultMap.put("triggerName", "");
            resultMap.put("type", "5");
            resultList.add(resultMap);
        }
        
        //关联查询触发器内容、触发器名称、表名
        String selectSql = "select a.text, UPPER(b.TRIGGER_NAME) as triggername, c.tablename from all_source a, all_triggers b, (select UPPER(tablename) as tablename from workflow_bill where id < 0 and UPPER(tablename) like 'FORMTABLE_MAIN%' ";
        selectSql += " union select UPPER(tablename) as tablename from workflow_billdetailtable where billid < 0 and UPPER(tablename) like 'FORMTABLE_MAIN%') c";
        selectSql += " where a.type='TRIGGER' and a.owner = '" + owner + "' and b.owner = '" + owner + "'";
        selectSql += " and a.name = b.TRIGGER_NAME and b.TABLE_NAME = c.tablename order by c.tablename, a.line";
        
        recordSet.executeSql(selectSql);
        
        //定义一个Map<触发器名, 表名>
        Map<String, String> newTriggerNameMap = new HashMap<String, String>();
        //定义一个Map<触发器名, 触发器内容>
        Map<String, String> newTriggerTextMap = new HashMap<String, String>();
        
        //定义一个Map<表名, 触发器名>
        Map<String, String> tabTriggerMap = new HashMap<String, String>();
        
        //多个触发器时放入showMessageMap
        Map<String, String> showMessageMap = new HashMap<String, String>();
        //一个自定义表单含多个触发器时赋值
        String moreTriName = "";
        //拿到所有表单表（formtable_main%）查询的触发器信息
        while(recordSet.next()){
            String tableName = recordSet.getString("tablename");
            String triggerName = recordSet.getString("triggername");
            String triggerText = recordSet.getString("text");
            String text = "";
            //判断每个表单是否只有一个触发器，如果不是则拼成moreTriName并放入showMessageMap
            if(tabTriggerMap.containsKey(tableName)){
                if(!tabTriggerMap.get(tableName).equals(triggerName) && !moreTriName.contains(triggerName)){
                    moreTriName = moreTriName + "; " + triggerName;
                    showMessageMap.put(tableName, moreTriName);
                }
                newTriggerNameMap.put(triggerName, tableName);
            }else{
                newTriggerNameMap.put(triggerName, tableName);
                tabTriggerMap.put(tableName, triggerName);
                moreTriName = tabTriggerMap.get(tableName);
            }
            
            //将触发器内容放入newTriggerTextMap
            if(newTriggerTextMap.containsKey(triggerName)){
                text = replaceTriggerText(newTriggerTextMap.get(triggerName) + " " + triggerText);
                newTriggerTextMap.put(triggerName, text);
            }else{
                text = replaceTriggerText(triggerText);
                newTriggerTextMap.put(triggerName, text);
            }
        }
        
        //自定义表单含有多个触发器时，遍历放入resultList
        for (Map.Entry<String, String> entry : showMessageMap.entrySet()) {
            String tableName = entry.getKey();
            String moreTriggerName = entry.getValue();
            Map resultMap = new HashMap<String, String>();
            resultMap.put("showMessage", tableName + "表中存在多个触发器:" + moreTriggerName + " ，请单个修复");
            resultMap.put("buttonname","修复");
            resultMap.put("tableName",tableName);
            resultMap.put("triggerName", moreTriggerName);
            resultMap.put("type", "3");
            resultList.add(resultMap);
        }
        
        //遍历表对应触发器内容，判断与标准是否相符
        for(Map.Entry<String, String> entry : newTriggerTextMap.entrySet()) {
            String triggerName = entry.getKey();
            String tableName = newTriggerNameMap.get(triggerName);
            String triggernameid = triggerName.substring(0, triggerName.lastIndexOf("_"));
            String triggerText1 = replaceTriggerText(triggerTextMap.get("formtable_main_ID_TR").replace("&triggername&", triggerName).replace("&table&", tableName).replace("&triggernameid&", triggernameid).replace("&user&", owner).toUpperCase().trim());
            String triggerText2 = replaceTriggerText(triggerTextMap.get("formtable_main_ID_TRIGGER").replace("&triggername&", triggerName).replace("&table&", tableName).replace("&triggernameid&", triggernameid).replace("&user&", owner).toUpperCase().trim());
            if(!triggerText1.equals(entry.getValue().toUpperCase().trim()) && !triggerText2.equals(entry.getValue().toUpperCase().trim()))
            {
                Map resultMap = new HashMap<String, String>();
                resultMap.put("showMessage", tableName + "表中触发器:" + triggerName + "内容与标准不符，请单个修复！");
                resultMap.put("buttonname","修复");
                resultMap.put("tableName",tableName);
                resultMap.put("triggerName",triggerName);
                resultMap.put("type", "4");
                resultList.add(resultMap); 
            }
        }
    }

    /**
     * 去除多余空格
     * @param triggerText triggerText
     * @return triggerText triggerText
     */
    private String replaceTriggerText(String triggerText) {
        return triggerText.replace("  ", " ").replace("  ", " ").replace("  ", " ").replace("  ", " ").replace("  ", " ")
        .replace("  ", " ").replace("  ", " ").replace("  ", " ").replace("  ", " ").replace("  ", " ");
    }

    /**
     * 检测几个系统表触发器数目以及内容
     * @param resultList
     * @param tableTriggerSql
     * @param table
     * @param triggerCount
     * @param user
     */
    private void checkTableTrigger(List resultList, String tableTriggerSql, String table, int triggerCount, String user) {
        //检测表对应触发器和标准有无差异
        recordSet.executeSql(tableTriggerSql.replace("%table%", table));
        
        //判断触发器个数是否相同
        if(recordSet.getCounts() != triggerCount){
            Map resultMap = new HashMap<String, String>();
            resultMap.put("showMessage", table + "表中触发器数量与标准有差异，请单个修复！");
            resultMap.put("buttonname","修复");
            resultMap.put("tableName",table);
            resultMap.put("type", "2");
            resultList.add(resultMap);
            return;
        }
        
        //重新实例化一个rs
        RecordSet rs1 = new RecordSet();
        Map<String, String> newTriggerMap = new HashMap<String, String>();
        String textSql = "select text from all_source where type='TRIGGER' AND name = '&table&' and owner = '" + user + "'";
        
        //判断对应触发器在标准中是否有
        while(recordSet.next()){
            String triggerName = recordSet.getString("trigger_name");
            if("WORKFLOW_BASE".equals(table) && !baseTriggerList.contains(triggerName)){
                Map resultMap = new HashMap<String, String>();
                resultMap.put("showMessage", table + "表中触发器:" + triggerName + " 在标准中没有，请单个修复！");
                resultMap.put("buttonname","修复");
                resultMap.put("tableName",table);
                resultMap.put("triggerName",triggerName);
                resultMap.put("type", "0");
                resultList.add(resultMap);
            }else if ("WORKFLOW_CURRENTOPERATOR".equals(table) && !currentTriggerList.contains(triggerName)){
                Map resultMap = new HashMap<String, String>();
                resultMap.put("showMessage", table + "表中触发器:" + triggerName + " 在标准中没有，请单个修复！");
                resultMap.put("buttonname","修复");
                resultMap.put("tableName",table);
                resultMap.put("triggerName",triggerName);
                resultMap.put("type", "0");
                resultList.add(resultMap);
            }else if ("WORKFLOW_REQUESTBASE".equals(table)){
                if(!requestbaseTrigger.equals(triggerName)){
                    Map resultMap = new HashMap<String, String>();
                    resultMap.put("showMessage", table + "表中触发器:" + triggerName + " 在标准中没有，请单个修复！");
                    resultMap.put("buttonname","修复");
                    resultMap.put("tableName",table);
                    resultMap.put("triggerName",triggerName);
                    resultMap.put("type", "0");
                    resultList.add(resultMap);
                }
            }
            
            rs1.executeSql(textSql.replace("&table&", triggerName));
            String text = "";
            while(rs1.next()){
                if(newTriggerMap.containsKey(triggerName)){
                    text = replaceTriggerText(newTriggerMap.get(triggerName) + " " + rs1.getString("text"));
                    newTriggerMap.put(triggerName, text);
                }else{
                    text = replaceTriggerText(rs1.getString("text"));
                    newTriggerMap.put(triggerName, text);
                }
            }
        }
        
        //遍历表对应触发器内容，判断与标准是否相符
        for(Map.Entry<String, String> entry : newTriggerMap.entrySet()) {
            String triggerName = entry.getKey();
            if(triggerTextMap.containsKey(triggerName)){
                if(!replaceTriggerText(triggerTextMap.get(triggerName).replace("&user&", user).toUpperCase().trim()).equals(entry.getValue().toUpperCase().trim())){
                    Map resultMap = new HashMap<String, String>();
                    resultMap.put("showMessage", table + "表中触发器:" + triggerName + "内容与标准不符，请单个修复！");
                    resultMap.put("buttonname","修复");
                    resultMap.put("tableName",table);
                    resultMap.put("triggerName",triggerName);
                    resultMap.put("type", "1");
                    resultList.add(resultMap); 
                }
            }
        }
        
    }

    /**
     * 检测sqlserver环境下触发器
     * @param resultList
     * @param tableTriggerSql
     * @param baseTable
     * @param triggerCount
     */
    private void sqlServerTriCheck(List resultList, String tableTriggerSql, String baseTable, int triggerCount) {
        RecordSet rs1 = new RecordSet();
        recordSet.executeSql(tableTriggerSql.replace("%table%", baseTable));
        //判断触发器个数是否相同
        if(recordSet.getCounts() != triggerCount){
            Map resultMap = new HashMap<String, String>();
            resultMap.put("showMessage", baseTable + "表中触发器数量与标准有差异，请单个修复！");
            //resultMap.put("buttonname","修复");
            resultMap.put("tableName",baseTable);
            resultMap.put("type", "2");
            resultList.add(resultMap);
        }else{
            //如果不为空，则判断触发器名称是否符合标准
            while(recordSet.next()){
                String triggerName = Util.null2String(recordSet.getString("trigger_name")).toUpperCase();
                
                if("WORKFLOW_BASE".equals(baseTable) && !"WORKFLOWBASETIMESTAMP_TRIGGER".equals(triggerName) && !"WORKFLOW_BASE_GETPINYIN".equals(triggerName))
                {
                    Map resultMap = new HashMap<String, String>();
                    resultMap.put("showMessage", baseTable + "表中触发器:" + triggerName + " 在标准中没有，请单个修复！");
                    //resultMap.put("buttonname","修复");
                    resultMap.put("tableName",baseTable);
                    resultMap.put("triggerName",triggerName);
                    resultMap.put("type", "0");
                    resultList.add(resultMap);
                }else if("WORKFLOW_REQUESTBASE".equals(baseTable) && !"WORKFLOW_REQUESTBASE_GETPINYIN".equals(triggerName)){
                    Map resultMap = new HashMap<String, String>();
                    resultMap.put("showMessage", baseTable + "表中触发器:" + triggerName + " 在标准中没有，请单个修复！");
                    //resultMap.put("buttonname","修复");
                    resultMap.put("tableName",baseTable);
                    resultMap.put("triggerName",triggerName);
                    resultMap.put("type", "0");
                    resultList.add(resultMap);
                }else{
                    String text = "";
                    rs1.executeSql("exec sp_helptext '" + triggerName + "'");
                    while(rs1.next()){
                        text = text + rs1.getString("Text");
                    }
                    if(!replaceTriggerText(serverTriggerTextMap.get(triggerName)).toUpperCase().equals(replaceTriggerText(text).toUpperCase())){
                        Map resultMap = new HashMap<String, String>();
                        resultMap.put("showMessage", baseTable + "表中触发器:" + triggerName + "内容与标准不符，请单个修复！");
                        //resultMap.put("buttonname","修复");
                        resultMap.put("tableName",baseTable);
                        resultMap.put("triggerName",triggerName);
                        resultMap.put("type", "1");
                        resultList.add(resultMap); 
                    }
                }
            }
        }
    }

    @Override
    public JSONObject fixData(HashMap dataMap) {
        String dbType = recordSet.getDBType();
        if(dataMap!= null && dataMap.get("divid")!= null){
            String tableName = (String)dataMap.get("tableName");
            String triggerName = (String)dataMap.get("triggerName");
            String type = (String)dataMap.get("type");
            
            tableName = Util.null2String(tableName).toUpperCase();
            triggerName = Util.null2String(triggerName).toUpperCase();
            
            Connection jdbc_conn=null;
            String driverClasses = getPropValue(GCONST.getConfigFile() , "DriverClasses");
            String poolName = GCONST.getServerName();
            //链接的url
            String url = getPropValue(GCONST.getConfigFile() , poolName + ".url");
            //链接的用户名
            String user = getPropValue(GCONST.getConfigFile() , poolName + ".user");
            //链接的密码
            String password = getPropValue(GCONST.getConfigFile() , poolName + ".password");
            
            try {
                Driver driver = (Driver)Class.forName(driverClasses).newInstance();
                DriverManager.registerDriver(driver);
                Properties props = new Properties();
                props.put("user", user);
                props.put("password", password);
                props.put("CHARSET", "ISO");
                jdbc_conn = DriverManager.getConnection(url, props);
                statement = jdbc_conn.createStatement();
                
                setRecordSet();
                (new BaseBean()).writeLog("TriggerCheck fixData start");
                List resultList = new ArrayList();
                Map resultMap = new HashMap<String,String>();
                String result = resultOk;
                String divid = (String)dataMap.get("divid");
                
                user = user.toUpperCase();
                
                if("oracle".equals(dbType)){
                  //根据对应表名来查询有哪些触发器
                    String tableTriggerSql = "select trigger_name from all_triggers where table_name = '%table%' and owner = '" + user + "'";
                    
                    //type为2则是WORKFLOW_BASE等表内触发器数目与标准不同，可以根据表名全drop掉，再添加标准
                    if("2".equals(type)){
                        recordSet.executeSql(tableTriggerSql.replace("%table%", tableName));
                        
                        while(recordSet.next()){
                            String striggerName = Util.null2String(recordSet.getString("trigger_name")).toUpperCase();
                            if(!triggerTextMap.containsKey(striggerName)){
                                //删除掉不在标准的触发器
                                recordSet.executeSql("DROP TRIGGER " + striggerName);
                            }
                        }
                        
                        //增加或替换标准的触发器
                        addOrReplaceTableTrigger(tableName, "", user);
                    }
                    //type为0则是WORKFLOW_BASE等表内触发器在标准中没有，可以drop删除
                    else if("0".equals(type)){
                        if(!triggerTextMap.containsKey(triggerName)){
                            recordSet.executeSql("DROP TRIGGER " + triggerName);
                        }
                    }
                    //type为1则是WORKFLOW_BASE等表内触发器内容与标准不同，可以直接修改为标准
                    else if("1".equals(type))
                    {
                        //增加或替换标准的触发器
                        addOrReplaceTableTrigger(tableName, triggerName, user);
                    }
                    
                    //type为3则是formtable_main自定义表表内触发器存在多个，可以根据表名全drop掉，再添加标准
                    else if("3".equals(type))
                    {
                        recordSet.executeSql(tableTriggerSql.replace("%table%", tableName));
                        while(recordSet.next()){
                            String striggerName = Util.null2String(recordSet.getString("trigger_name")).toUpperCase();
                            //删除掉触发器
                            (new BaseBean()).writeLog("TriggerCheck fixData dropSql:" + "DROP TRIGGER " + striggerName);
                            recordSet.executeSql("DROP TRIGGER " + striggerName);
                        }
                        
                        //根据是否是主表来确定触发器名称
                        if(tableName.contains("_DT")){
                            triggerName = tableName + "_ID_TR";
                        }else{
                            triggerName = tableName + "_ID_TRIGGER";
                        }
                        //增加或替换为标准的触发器
                        addOrReplaceTableTrigger(tableName, triggerName, user);
                    }
                    //type为4则是formtable_main自定义表单内触发器内容与标准不同，可以直接修改为标准
                    else if("4".equals(type))
                    {
                        //如果触发器名称不满足规则
                        if(!triggerName.equals(tableName + "_ID_TR") && !triggerName.equals(tableName + "_ID_TRIGGER"))
                        {
                            //删除掉触发器
                            recordSet.executeSql("DROP TRIGGER " + triggerName);
                            
                            //根据是否是主表来确定触发器名称
                            if(tableName.contains("_DT")){
                                triggerName = tableName + "_ID_TR";
                            }else{
                                triggerName = tableName + "_ID_TRIGGER";
                            }
                        }
                        
                        //增加或替换标准的触发器
                        addOrReplaceTableTrigger(tableName, triggerName, user);
                    }
                    //type为5则是formtable_main自定义表单内没有触发器，可以直接新建标准
                    else if("5".equals(type))
                    {
                        //根据是否是主表来确定触发器名称
                        if(tableName.contains("_DT")){
                            triggerName = tableName + "_ID_TR";
                        }else{
                            triggerName = tableName + "_ID_TRIGGER";
                        }
                        
                        //增加或替换标准的触发器
                        addOrReplaceTableTrigger(tableName, triggerName, user);
                    }else{
                        return checkData(new HashMap());
                    }
                }else if("sqlserver".equals(dbType)){
                    
                    //根据对应表名来查询有哪些触发器
                    String tableTriggerSql = "EXEC sp_helptrigger '%table%'";
                    
                    //type为2则是WORKFLOW_BASE等表内触发器数目与标准不同，可以根据表名全drop掉，再添加标准
                    if("2".equals(type)){
                        recordSet.executeSql(tableTriggerSql.replace("%table%", tableName));
                        
                        while(recordSet.next()){
                            String striggerName = Util.null2String(recordSet.getString("trigger_name")).toUpperCase();
                            if(!serverTriggerTextMap.containsKey(striggerName)){
                                //删除掉不在标准的触发器
                                recordSet.executeSql("DROP TRIGGER " + striggerName);
                            }
                        }
                        
                        //增加标准的触发器
                        sqlServerAddOrReplaceTableTrigger(tableName, "", user);
                    }
                    //type为0则是WORKFLOW_BASE等表内触发器在标准中没有，可以drop删除
                    else if("0".equals(type)){
                        if(!serverTriggerTextMap.containsKey(triggerName)){
                            recordSet.executeSql("DROP TRIGGER " + triggerName);
                        }
                    }
                    //type为1则是WORKFLOW_BASE等表内触发器内容与标准不同，可以直接修改为标准
                    else if("1".equals(type))
                    {
                        //修改标准的触发器
                        sqlServerAddOrReplaceTableTrigger(tableName, triggerName, user);
                    }
                }else{
                    (new BaseBean()).writeLog("TriggerCheck fixData end");
                    return checkData(new HashMap());
                }
                
                resultMap.put("result",resultOk);
                resultMap.put("fixMessage","修复成功");
                resultMap.put("divid",divid);
                resultList.add(resultMap);
                (new BaseBean()).writeLog("TriggerCheck fixData end");
                return formatReturnJsonFix(result, resultList,this);
                
            } catch (Exception e) {
                (new BaseBean()).writeLog("TriggerCheck fixData statement Exception");
                e.printStackTrace();
            }
        }
        return checkData(new HashMap());
    }

    /**
     * 根据表名、触发器名称来新增或者替换成标准的触发器内容
     * @param tableName
     * @param triggerName
     * @param user
     * @throws SQLException 
     */
    private void addOrReplaceTableTrigger(String tableName, String triggerName, String user) throws SQLException {
        if("WORKFLOW_BASE".equals(tableName)){
            if("".equals(triggerName)){
                //执行修复sql
                executeAddOrReplaceSql("WORKFLOW_BASE_GETPINYIN", user);
                executeAddOrReplaceSql("WORKFLOW_BASE_TRIGGER", user);
                executeAddOrReplaceSql("WORKFLOWBASETIMESTAMP_TRI", user);
            }else{
                executeAddOrReplaceSql(triggerName, user);
            }
            
        }else if("WORKFLOW_CURRENTOPERATOR".equals(tableName)){
            if("".equals(triggerName)){
                //执行修复sql
                executeAddOrReplaceSql("T_WF_CUROPT_TRIGGER", user);
                executeAddOrReplaceSql("CURRENTOPERATORCHG_TRIGGER", user);
            }else{
                executeAddOrReplaceSql(triggerName, user);
            }
            
        }else if("WORKFLOW_REQUESTBASE".equals(tableName)){
            //执行修复sql
            executeAddOrReplaceSql("WORKFLOW_REQUESTBASE_GETPINYIN", user);
        }else{
            String triggernameid = triggerName.substring(0, triggerName.lastIndexOf("_"));
            //自定义表单触发器
            String triSql = "CREATE OR REPLACE ";
            triSql += triggerTextMap.get("formtable_main_ID_TR");
            
            triSql = triSql.replace("&triggername&", triggerName).replace("&table&", tableName).replace("&triggernameid&", triggernameid);
            //执行新增或替换触发器sql
            (new BaseBean()).writeLog("TriggerCheck fixData addOrReplaceSql:" + triSql.replace("&user&", user));
            statement.executeUpdate(triSql.replace("&user&", user));
        }
    }

    /**
     * 执行新增或者替换触发器sql
     * @param triggerName
     * @param user
     * @throws SQLException 
     */
    private void executeAddOrReplaceSql(String triggerName, String user) throws SQLException {
        String triSql = "CREATE OR REPLACE ";
        triSql += triggerTextMap.get(triggerName);
        //执行新增或替换触发器sql
        (new BaseBean()).writeLog("TriggerCheck fixData executeAddOrReplaceSql:" + triSql.replace("&user&", user));
        statement.executeUpdate(triSql.replace("&user&", user));
    }
    
    private void sqlServerAddOrReplaceTableTrigger(String tableName, String triggerName, String user) throws SQLException {
        if("WORKFLOW_BASE".equals(tableName)){
            if("".equals(triggerName)){
                //执行修复sql
                sqlServerExecuteAddOrReplaceSql("WORKFLOWBASETIMESTAMP_TRIGGER", user, "CREATE ");
                sqlServerExecuteAddOrReplaceSql("WORKFLOW_BASE_GETPINYIN", user, "CREATE ");
            }else{
                sqlServerExecuteAddOrReplaceSql(triggerName, user, "ALTER ");
            }
            
        }else if("WORKFLOW_REQUESTBASE".equals(tableName)){
            if("".equals(triggerName)){
                sqlServerExecuteAddOrReplaceSql("WORKFLOW_REQUESTBASE_GETPINYIN", user, "CREATE ");
            }else{
                sqlServerExecuteAddOrReplaceSql("WORKFLOW_REQUESTBASE_GETPINYIN", user, "ALTER ");
            }
            
        }
    }

    private void sqlServerExecuteAddOrReplaceSql(String triggerName, String user, String excuHead) throws SQLException {
         String triSql = excuHead + serverTriggerTextMap.get(triggerName);
        //执行新增或替换触发器sql
        (new BaseBean()).writeLog("TriggerCheck fixData executeAddOrReplaceSql:" + triSql.replace("&user&", user));
        statement.executeUpdate(triSql.replace("&user&", user));
    }

    @Override
    public String getClassDesc() {
        return "检测数据库表是否有额外的触发器，与标准触发器内容是否相同";
    }

    @Override
    public String getClassName() {
        return "数据库表触发器检测";
    }

}
